﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Data.SqlServerCe;
using System.Data.SqlTypes;
using OpenNETCF.Desktop.Communication;

namespace RefOffDesktop
{
    /*
     * Class: RefOff
     * 
     * Use: Main screen of the RefOff application, primarily displaying a
     *      List of all Matches in the system. Also allows importing of new
     *      data, and updating of Referee Profile information
     *      
     * Author: Mike Skaife, mcs3@aber.ac.uk
     */ 
    public partial class RefOff : Form
    {
        //Details required to connect to database
        string conString;
        SqlCeConnection conn;

        //Connection string for desktop database
        String desktop_connection = @"Data Source=RefOff_DB.sdf";

        //Connection string for PDA database on device
        String pda_connection = @"Data Source=C:\RefOffPDA_DB.sdf";

        //Arrays to hold all objects from all matches
        public List<Match> matches;
        public List<Goal> goals;
        public List<Caution> cautions;
        public List<SendOff> sendoffs;
        public List<Player> players;
        public List<Club> clubs;

        //Haversine object used to calculate distances
        //between Virtual Pitch incidents and whistles
        private Haversine hav;

        //String indicating where PDA data is to be imported to
        public ImportLocation importLocation;

        //Progress bar displayed during data import
        public ProgressBar progress;

        //Referee Profile form used to update personal details of referee
        public RefereeProfile prof;

        //DataSets used to store data temporarily during data import
        public DataSet match_ds;
        public DataSet club_ds;
        public DataSet player_ds;
        public DataSet caution_ds;
        public DataSet sendoff_ds;
        public DataSet goal_ds;
        public DataSet tempMatch_ds;
        public DataSet tempClub_ds;
        public DataSet tempPlayer_ds;
        public DataSet tempCaution_ds;
        public DataSet tempSendoff_ds;
        public DataSet tempGoal_ds;

        //Variables used during data import operation
        int numOfImportedMatches;
        int numOfImportedClubs;
        int numOfImportedPlayers;
        int numOfImportedCautions;
        int numOfImportedSendoffs;
        int numOfImportedGoals;

        //Next available primary key values in desktop
        //database tables, used when importing data from PDA
        public int nextMatchId = 0;
        public int nextClubId = 0;
        public int nextPlayerId = 0;
        public int nextCautionId = 0;
        public int nextSendoffId = 0;
        public int nextGoalId = 0;

        //Count of the number of matches displayed on this screen
        public int matchesDisplayed = 0;
        
        public RefOff()
        {
            InitializeComponent();
            matches = new List<Match>();
            goals = new List<Goal>();
            cautions = new List<Caution>();
            sendoffs = new List<SendOff>();
            players = new List<Player>();
            clubs = new List<Club>();
            hav = new Haversine();
            importLocation = new ImportLocation();
            progress = new ProgressBar();
            prof = new RefereeProfile(this);

            //Get hold of all players and store in array
            getPlayerDetails(desktop_connection);
            //Get hold of all clubs and store in array
            getClubDetails(desktop_connection);
            //Get hold of all goals and store in array
            getGoalDetails(desktop_connection);
            //Get hold of all cautions and store in array
            getCautionDetails(desktop_connection);
            //Get hold of all sending offs and store in array
            getSendOffDetails(desktop_connection);

            //Get hold of referee deatils and store in Referee Profile form
            getRefereeDetails(desktop_connection);

            //Get hold of all matches and store in array
            getMatches(desktop_connection);

            //Find next available primary key values in all database tables
            getMaximumDBValues(desktop_connection);

            //Colour the list of matches displayed on RefOff screen
            setListViewColors();
        }

        //Method used to get details of all matches from database, and
        //store them in a global array
        void getMatches(String connection_string)
        {
            //Connect to the database
            connectToDatabase(connection_string);

            //Pull out all match data and stick it in a dataset
            string command = @"SELECT match_id, home_club_id, away_club_id, competition, football_association, date FROM match";
            SqlCeDataAdapter da = new SqlCeDataAdapter(command, conn);
            DataSet ds = new DataSet();
            da.Fill(ds, "match_details");
            DataTable dt = ds.Tables["match_details"];

            //Count how many matches exist in the DataSet
            int numOfMatches = ds.Tables[0].Rows.Count;

            //Create a new Match object, and thus a new Match Details screen
            Match newMatch;

            //Go through each match in the DataSet
            for (int i = 0; i < numOfMatches; i++)
            {
                //Initialise the new Match and Match Details screen
                newMatch = new Match();

                //Set the id of the Match
                newMatch.match_id = (int)ds.Tables[0].Rows[i]["match_id"];
                
                //Check a date exists for the Match
                if (!(ds.Tables[0].Rows[i]["date"] is DBNull))
                {
                    //Set the date of the Match in its database format
                    newMatch.db_date = (DateTime)ds.Tables[0].Rows[i]["date"];
                }
                
                //Set the date of the match formatted as dd/mm/yyyy
                newMatch.date = newMatch.db_date.ToString("d");

                //Set other details for the Match
                newMatch.homeId = (int)ds.Tables[0].Rows[i]["home_club_id"];
                newMatch.homeTeam = getTeamDetails(newMatch.homeId, conn);
                newMatch.homeSquad = getSquadDetails(newMatch.homeId, conn);
                newMatch.awayId = (int)ds.Tables[0].Rows[i]["away_club_id"];
                newMatch.awayTeam = getTeamDetails(newMatch.awayId, conn);
                newMatch.awaySquad = getSquadDetails(newMatch.awayId, conn);
                newMatch.competition = (string)ds.Tables[0].Rows[i]["competition"];
                newMatch.association = (string)ds.Tables[0].Rows[i]["football_association"];
                
                //Add the newly created Match to the global list of Matches
                matches.Add(newMatch);

                //Get hold of all the Goals within the Match
                getGoals(newMatch.match_id, conn);

                //Get hold of all the Cautions within the Match
                getCautions(newMatch.match_id, conn);

                //Get hold of all the Sending Offs within the Match
                getSendOffs(newMatch.match_id, conn);

                //Get hold of all the Players within the Match, for both teams
                getMatchPlayers(newMatch.homeId, newMatch.awayId, newMatch);

                //Create all the Virtual Pitch Incidents to be displayed for this Match
                createVPIncidents(newMatch);
                  
                //Colour the lists in the tabs of the Match Details screen
                newMatch.setListViewColors();

                //Indicate that another Match has been added to the system
                matchesDisplayed++;
            }

            ListViewItem match;

            for (int j = 0; j < numOfMatches; j++)
            {
                match = new ListViewItem();
                match.Text = matches[j].date;
                match.SubItems.Add(matches[j].homeTeam);
                match.SubItems.Add(matches[j].awayTeam);
                match.SubItems.Add(matches[j].competition);
                refOffLst.Items.Add(match);
            }

            closeDatabaseConnection();
        }

        //Method used to get hold of the name of a specified Club from the database
        String getTeamDetails(int team_id, SqlCeConnection conn)
        {
            //Variable to be returned
            String name = "";

            //Create new reader to access data
            SqlCeDataReader reader;
            
            //Create and execute SQL command to obtain name of club based on
            //method input parameter representing id of club
            string command = @"SELECT name FROM club WHERE club_id = @teamID;";
            using (SqlCeCommand team_name = new SqlCeCommand(command, conn))
            {
                team_name.Parameters.AddWithValue("@teamID", team_id);
                reader = team_name.ExecuteReader();
            }

            //Read data from the reader to obtain the name of the club
            while (reader.Read())
            {
                name = reader[0].ToString();
            }

            //Close the reader
            reader.Close();

            //Return the name of the team
            return name;
        }

        //Method used to get hold of the squad of a particular Club involved in a Match
        String getSquadDetails(int team_id, SqlCeConnection conn)
        {
            //Variable to be returned
            String squad = "";

            //Create new reader to access data
            SqlCeDataReader reader;
            
            //Create and execute SQL command to obtain squad details for specified
            //club using input parameter representing club id
            string command = @"SELECT team FROM club WHERE club_id = @teamID;";
            using (SqlCeCommand team_squad = new SqlCeCommand(command, conn))
            {
                team_squad.Parameters.AddWithValue("@teamID", team_id);
                reader = team_squad.ExecuteReader();
            }

            //Access data from reader
            while (reader.Read())
            {
                squad = reader[0].ToString();
            }

            //Close reader
            reader.Close();

            //Return squad details
            return squad;
        }

        //Method used to get hold of all the Goals relating to a specified Match
        void getGoals(int match_id, SqlCeConnection conn)
        {
            //Create a new Goal item to be displayed
            //in the Match Details screen
            ListViewItem goal;

            //Initialise Match object involved and get its id
            Match goalMatch = null;
            int thisMatch = match_id;

            //Loop through all the Matches stored in the application
            for (int i = 0; i < matches.Count; i++)
            {
                if (matches[i].match_id == thisMatch)
                {
                    //When the specified Match is found, reference it
                    goalMatch = matches[i];
                }
            }

            //Count how many Goals exist in the system for all Matches
            int numOfGoals = goals.Count;

            //Go through all Goals
            for (int j = 0; j < numOfGoals; j++)
            {                
                //Get hold of the match_id for this Goal
                int matchid = goals[j].match_id;

                //If it exists within the specified Match...
                if (matchid == thisMatch)
                {                
                    //Initialise the new goal tab item
                    goal = new ListViewItem();

                    //Set the name of the player who scored the goal
                    goal.Text = goals[j].player_name;

                    //Add Club name for the goal
                    goal.SubItems.Add(goals[j].club);

                    //Indicate whether this is an own goal or not
                    if (goals[j].own_goal == 1)
                    {
                        goal.SubItems.Add("Yes");
                    }
                    else
                    {
                        goal.SubItems.Add("No");
                    }

                    //Tag the item with the id of the Goal for future reference
                    goal.Tag = goals[j].goal_id;

                    //Add the new item to the list of Goals on the Match Details screen,
                    //and to the list of goals stored within the relevant Match
                    goalMatch.matchGoalLst.Items.Add(goal);
                    goalMatch.matchGoals.Add(goals[j]);
                }                
            }
        }

        //Method used to get hold of all the Cautions relating to a specified Match
        void getCautions(int match_id, SqlCeConnection conn)
        {
            //Create a new Caution item to be displayed
            //in the Match Details screen
            ListViewItem caution;

            //Initialise Match object involved and get its id
            Match cautionMatch = null;
            int thisMatch = match_id;
            
            //Loop through all the Matches stored in the application
            for (int i = 0; i < matches.Count; i++)
            {
                if (matches[i].match_id == thisMatch)
                {
                    //When the specified Match is found, reference it
                    cautionMatch = matches[i];
                }
            }

            //Count how many Cautions exist in the system for all Matches
            int numOfCautions = cautions.Count;

            //Go through all Cautions
            for (int j = 0; j < numOfCautions; j++)
            {
                //Get hold of the match_id for this Caution
                int matchid = cautions[j].match_id;

                //If it exists within the specified Match...
                if (matchid == thisMatch)
                {
                    //Initialise the new caution tab item
                    caution = new ListViewItem();

                    //Set details to be displayed for the Caution within
                    //the Match Details screen
                    caution.Text = cautions[j].player_name;
                    caution.SubItems.Add(cautions[j].club);
                    caution.SubItems.Add(cautions[j].caution_code);
                    caution.SubItems.Add(cautions[j].c1_code);

                    //Add the new item to the list of Cautions on the Match Details screen,
                    //and to the list of cautions within the relevant Match
                    cautionMatch.matchCautionLst.Items.Add(caution);
                    cautionMatch.matchCautions.Add(cautions[j]);
                }
            }
        }

        //Method used to get hold of all the Sending Offs relating to a specified Match
        void getSendOffs(int match_id, SqlCeConnection conn)
        {
            //Create a new Sending Off item to be displayed
            //in the Match Details screen
            ListViewItem sendOff;

            //Initialise Match object involved and get its id
            Match sendOffMatch = null;
            int thisMatch = match_id;

            //Loop through all the Matches stored in the application
            for (int i = 0; i < matches.Count; i++)
            {
                if (matches[i].match_id == thisMatch)
                {
                    //When the specified Match is found, reference it
                    sendOffMatch = matches[i];
                }
            }

            //Count how many Sending Offs exist in the system for all Matches
            int numOfSendOffs = sendoffs.Count;

            //Go through all Sending Offs
            for (int j = 0; j < numOfSendOffs; j++)
            {
                //Get hold of the match_id for this Sending Off
                int matchid = sendoffs[j].match_id;

                //If it exists within the specified Match...
                if (matchid == thisMatch)
                {
                    //Initialise the new Sending Off item
                    sendOff = new ListViewItem();

                    //Set details to be displayed for the Sending Off within
                    //the Match Details screen
                    sendOff.Text = sendoffs[j].player_name;
                    sendOff.SubItems.Add(sendoffs[j].club);
                    sendOff.SubItems.Add(sendoffs[j].sendoff_code);

                    //Add the new item to the list of Sending Offs on the Match Details screen,
                    //and to the list of cautions within the relevant Match
                    sendOffMatch.matchSendoffLst.Items.Add(sendOff);
                    sendOffMatch.matchSendOffs.Add(sendoffs[j]);
                }
            }
        }

        //Method used to return the full name of a player from their database id,
        //in the form: Number. Firstname Surname
        String getPlayerName(int player_id, SqlCeConnection conn)
        {
            //Variables making up the name to be returned
            String name = "";
            String number = "";
            String forename = "";
            String surname = "";

            //Create new reader to access data
            SqlCeDataReader reader;

            //Create and execute SQL command to obtain details of specified player
            string command = @"SELECT number, forename, surname FROM player WHERE player_id = @playerID;";
            using (SqlCeCommand team_name = new SqlCeCommand(command, conn))
            {
                team_name.Parameters.AddWithValue("@playerID", player_id);
                reader = team_name.ExecuteReader();
            }

            //Access the reader
            while (reader.Read())
            {
                //Set variables to required database values
                number = reader[0].ToString();
                forename = reader[1].ToString();
                surname = reader[2].ToString();
            }

            //Close the reader
            reader.Close();

            //Create the full name from the other variables
            name = number + ". " + " " + forename + " " + surname;

            //Return the formatted name of the player
            return name;
        }

        //Get hold of the surname of a specified player within the database
        String getPlayerSurname(int player_id, SqlCeConnection conn)
        {
            //Initialise variable to be returned
            String surname = "";

            //Create a new reader to access the data
            SqlCeDataReader reader;
            
            //Create and execute SQL command to obtain surname of specified player
            string command = @"SELECT surname FROM player WHERE player_id = @playerID;";
            using (SqlCeCommand sur_name = new SqlCeCommand(command, conn))
            {
                sur_name.Parameters.AddWithValue("@playerID", player_id);
                reader = sur_name.ExecuteReader();
            }

            //Access data using reader
            while (reader.Read())
            {
                //Set the variable with the value from the database
                surname = reader[0].ToString();
            }

            //Close the reader
            reader.Close();

            //Return the players surname
            return surname;
        }

        //Get hold of the firstname of a specified player within the database
        String getPlayerFirstname(int player_id, SqlCeConnection conn)
        {
            //Initialise variable to be returned
            String firstname = "";

            //Create a new reader to access the data
            SqlCeDataReader reader;

            //Create and execute SQL command to obtain firstname of specified player
            string command = @"SELECT forename FROM player WHERE player_id = @playerID;";
            using (SqlCeCommand first_name = new SqlCeCommand(command, conn))
            {
                first_name.Parameters.AddWithValue("@playerID", player_id);
                reader = first_name.ExecuteReader();
            }

            //Access data using reader
            while (reader.Read())
            {
                //Set the variable with the value from the database
                firstname = reader[0].ToString();
            }

            //Close the reader
            reader.Close();

            //Return the players firstname
            return firstname;
        }

        //Method to obtain the name of a club based on the id of one of its players
        String getClubName(int player_id, SqlCeConnection conn)
        {
            //Initialise variable to be returned
            String clubName = "";

            //Create new reader to access data
            SqlCeDataReader reader;

            //Create and execute SQL command to access id of Club from id of Player
            string command = @"SELECT club_id FROM player WHERE player_id = @playerID;";
            using (SqlCeCommand clubID = new SqlCeCommand(command, conn))
            {
                clubID.Parameters.AddWithValue("@playerID", player_id);
                reader = clubID.ExecuteReader();
            }

            //Access reader to obtain data
            while (reader.Read())
            {
                //Create another reader
                SqlCeDataReader reader2;

                //Obtain relevant Club id from first reader
                int club = Int32.Parse(reader[0].ToString());

                //Create and execute SQL command to obtain name of Club from its id
                string command2 = @"SELECT name FROM club WHERE club_id = @clubID;";
                using (SqlCeCommand club_name = new SqlCeCommand(command2, conn))
                {
                    club_name.Parameters.AddWithValue("@clubID", club);
                    reader2 = club_name.ExecuteReader();
                }

                //Access second reader to obtain data
                while (reader2.Read())
                {
                    //Set variable to name of required Club
                    clubName = reader2[0].ToString();
                }
            }

            //Close the reader
            reader.Close();

            //Return the name of the Club
            return clubName;
        }

        //Method to obtain the name of a club based on its id
        String getOtherClubName(int club_id, SqlCeConnection conn)
        {
            //Initialise the variable to be returned
            String clubName = "";

            //Create a new reader to access data
            SqlCeDataReader reader;

            //Create and execute SQL command to obtain name of Club from its id
            string command = @"SELECT name FROM club WHERE club_id = @clubID;";
            using (SqlCeCommand clubID = new SqlCeCommand(command, conn))
            {
                clubID.Parameters.AddWithValue("@clubID", club_id);
                reader = clubID.ExecuteReader();
            }

            //Access reader to get data
            while (reader.Read())
            {                
                //Set variable to name of Club
                clubName = reader[0].ToString();                
            }

            //Close the reader
            reader.Close();

            //Return the name of the Club
            return clubName;
        }

        //Method to obtain the id of a Club in a match, based on
        //the id of the Match and the id of the other Club
        int getOtherClubId(int match_id, int club_id, SqlCeConnection conn)
        {
            //Initialise club id to be returned
            int club = 0;

            //Create new reader to access data
            SqlCeDataReader reader;

            //Create and execute SQL command to obtain all details of a match based
            //on its id from the database
            string command = @"SELECT * FROM match WHERE match_id = @matchID;";
            using (SqlCeCommand clubID = new SqlCeCommand(command, conn))
            {
                clubID.Parameters.AddWithValue("@matchID", match_id);
                reader = clubID.ExecuteReader();
            }

            //Access the reader to get the data
            while (reader.Read())
            {                
                //If the id of the home club in the match is not equal to the
                //club id passed in...
                if (Int32.Parse(reader[1].ToString()) != club_id)
                {
                    //...The 'other' club has this is
                    club = Int32.Parse(reader[1].ToString());
                }
                else
                {
                    //...Otherwise it has the id of the away club within the Match
                    club = Int32.Parse(reader[2].ToString());
                }
            }

            //Close the reader
            reader.Close();

            //Return the id of the Club
            return club;
        }

        //Method to get the id of a Club based on the id of one of its players
        int getClubId(int player_id, SqlCeConnection conn)
        {
            //Initialise the variable to be returned
            int club = 0;

            //Create a new reader to access data
            SqlCeDataReader reader;

            //Create and execute SQL command to obtain club id based on player id
            string command = @"SELECT club_id FROM player WHERE player_id = @playerID;";
            using (SqlCeCommand clubID = new SqlCeCommand(command, conn))
            {
                clubID.Parameters.AddWithValue("@playerID", player_id);
                reader = clubID.ExecuteReader();
            }

            //Access reader to get data
            while (reader.Read())
            {                
                //Set variable to value of obtained club id
                club = Int32.Parse(reader[0].ToString());                
            }

            //Close the reader
            reader.Close();

            //Return the id of the Club
            return club;
        }

        //Method to get hold of details of all Players from database
        void getPlayerDetails(String connection_string)
        {
            //Connect to the database
            connectToDatabase(connection_string);

            //Pull out all Player data and stick it in a dataset
            string command = @"SELECT * FROM player";
            SqlCeDataAdapter da = new SqlCeDataAdapter(command, conn);
            DataSet ds = new DataSet();
            da.Fill(ds, "player_details");
            DataTable dt = ds.Tables["player_details"];

            //Find out how many players exist in the DataSet
            int numOfPlayers = ds.Tables[0].Rows.Count;

            //Create a new Player object
            Player newPlayer;

            //Loop through each of the players in the DataSet
            for (int i = 0; i < numOfPlayers; i++)
            {
                //Initialise new Player object
                newPlayer = new Player();

                //Set the details for the new Player
                newPlayer.player_id = (int)ds.Tables[0].Rows[i]["player_id"];
                newPlayer.surname = (string)ds.Tables[0].Rows[i]["surname"];
                newPlayer.forename = (string)ds.Tables[0].Rows[i]["forename"];
                newPlayer.number = (int)ds.Tables[0].Rows[i]["number"];
                newPlayer.club_id = (int)ds.Tables[0].Rows[i]["club_id"];
                newPlayer.name = getPlayerName(newPlayer.player_id, conn);

                //Add new Player to the global list of players
                players.Add(newPlayer);
            }

            //Disconnect from the database
            closeDatabaseConnection();
        }

        //Method to get hold of details of all Clubs from database
        void getClubDetails(String connection_string)
        {
            //Connect to the database
            connectToDatabase(connection_string);

            //Pull out all Club data and stick it in a DataSet
            string command = @"SELECT * FROM club";
            SqlCeDataAdapter da = new SqlCeDataAdapter(command, conn);
            DataSet ds = new DataSet();
            da.Fill(ds, "club_details");
            DataTable dt = ds.Tables["club_details"];

            //Find out how many Clubs are in the DataSet
            int numOfClubs = ds.Tables[0].Rows.Count;

            //Create a new Club object
            Club newClub;

            //Loop through all the Clubs in the DataSet
            for (int i = 0; i < numOfClubs; i++)
            {
                //Initialise the new Club object
                newClub = new Club();

                //Set details for the new Club
                newClub.club_id = (int)ds.Tables[0].Rows[i]["club_id"];
                newClub.name = (string)ds.Tables[0].Rows[i]["name"];
                newClub.team = (string)ds.Tables[0].Rows[i]["team"];

                //Add the new Club to global list of clubs
                clubs.Add(newClub);
            }

            //Disconnect from database
            closeDatabaseConnection();
        }

        //Method to get hold of details of all Goals from database
        void getGoalDetails(String connection_string)
        {
            //Connect to the database
            connectToDatabase(connection_string);

            //Pull out all Goal data and stick it in a DataSet
            string command = @"SELECT * FROM goal";
            SqlCeDataAdapter da = new SqlCeDataAdapter(command, conn);
            DataSet ds = new DataSet();
            da.Fill(ds, "goal_details");
            DataTable dt = ds.Tables["goal_details"];

            //Find out how many Goals are in the DataSet
            int numOfGoals = ds.Tables[0].Rows.Count;

            //Create a new Goal object
            Goal newGoal;

            //Loop through all the Goals in the DataSet
            for (int i = 0; i < numOfGoals; i++)
            {
                //Initialise new Goal object
                newGoal = new Goal();

                //Set details for the new Goal
                newGoal.match_id = (int)ds.Tables[0].Rows[i]["match_id"];
                newGoal.goal_id = (int)ds.Tables[0].Rows[i]["goal_id"];
                newGoal.Tag = newGoal.goal_id;
                newGoal.own_goal = (int)ds.Tables[0].Rows[i]["own_goal"];
                newGoal.player_id = (int)ds.Tables[0].Rows[i]["player_id"];
                newGoal.player_name = getPlayerName(newGoal.player_id, conn);
                newGoal.time = (string)ds.Tables[0].Rows[i]["time"];
                newGoal.club = getClubName(newGoal.player_id, conn);
                newGoal.clubId = getClubId(newGoal.player_id, conn);
                newGoal.otherClubId = getOtherClubId(newGoal.match_id, newGoal.clubId, conn);
                newGoal.otherClub = getOtherClubName(newGoal.otherClubId, conn);

                //Add goalscorer to list of players associated with the Goal
                for (int j = 0; j < players.Count; j++)
                {
                    if (players[j].club_id == newGoal.clubId)
                    {
                        newGoal.goalMatchPlayers.Add(players[j]);
                    }
                    else if (players[j].club_id == newGoal.otherClubId)
                    {
                        newGoal.goalMatchPlayers.Add(players[j]);
                    }
                }

                //Add new Goal details to combo boxes on Goal form
                //and select default item
                newGoal.goalClubCombo.Items.Add(newGoal.club);
                newGoal.goalClubCombo.Items.Add(newGoal.otherClub);
                newGoal.goalClubCombo.SelectedIndex = 0;
                int index = 0;
                int counter = 0;
                String name;
                for (int k = 0; k < newGoal.goalMatchPlayers.Count; k++)
                {
                    if(newGoal.goalMatchPlayers[k].club_id == newGoal.clubId)
                    {                        
                        if (newGoal.goalMatchPlayers[k].player_id == newGoal.player_id)
                        {
                            index = counter;
                        }
                        name = getPlayerName(newGoal.goalMatchPlayers[k].player_id, conn);
                        newGoal.goalPlayerCombo.Items.Add(name);
                        counter++;
                    }
                }
                newGoal.goalPlayerCombo.SelectedIndex = index;

                //Indicate if the Goal is an own goal or not, on
                //the Goal form
                if (newGoal.own_goal == 1)
                {
                    newGoal.goalOwnGoalChk.Checked = true;
                }
                else
                {
                    newGoal.goalOwnGoalChk.Checked = false;
                }

                //Split the list of players associated with the Goal, into
                //a separate list for the home and away teams
                newGoal.setupPlayerArrays();

                //Add the new Goal to the global list of goals
                goals.Add(newGoal);
            }

            //Disconnect from the database
            closeDatabaseConnection();
        }

        //Method to get hold of details of all Cautions from database
        void getCautionDetails(String connection_string)
        {
            //Connect to the database
            connectToDatabase(connection_string);

            //Pull out data and stick it in a DataSet
            string command = @"SELECT * FROM caution";
            SqlCeDataAdapter da = new SqlCeDataAdapter(command, conn);
            DataSet ds = new DataSet();
            da.Fill(ds, "caution_details");
            DataTable dt = ds.Tables["caution_details"];

            //Find out how many Cautions are in the DataSet
            int numOfCautions = ds.Tables[0].Rows.Count;

            //Create new Caution object
            Caution newCaution;

            //Loop through all Cautions in DataSet
            for (int i = 0; i < numOfCautions; i++)
            {
                //Initialise new Caution object
                newCaution = new Caution();

                //Set details for the new Caution
                newCaution.match_id = (int)ds.Tables[0].Rows[i]["match_id"];
                newCaution.caution_id = (int)ds.Tables[0].Rows[i]["caution_id"];
                newCaution.caution_code = (string)ds.Tables[0].Rows[i]["caution_code"];
                
                //Set C1 code for Caution as required
                if (ds.Tables[0].Rows[i]["c1_code"] is DBNull)
                {
                    newCaution.c1_code = "-";
                }
                else
                {                    
                    newCaution.c1_code = (string)ds.Tables[0].Rows[i]["c1_code"];
                }

                //Continue setting details for Caution
                newCaution.player_id = (int)ds.Tables[0].Rows[i]["player_id"];
                newCaution.player_name = getPlayerName(newCaution.player_id, conn);
                newCaution.player_surname = getPlayerSurname(newCaution.player_id, conn);
                newCaution.player_firstname = getPlayerFirstname(newCaution.player_id, conn);
                newCaution.club = getClubName(newCaution.player_id, conn);
                newCaution.time = (string)ds.Tables[0].Rows[i]["time"];
                
                //Set GPS location for whistle event
                if (!(ds.Tables[0].Rows[i]["whistle_GPS"] is DBNull))
                {
                    newCaution.whistleGPS = (string)ds.Tables[0].Rows[i]["whistle_GPS"];
                }

                //Set GPs location for actual Caution incident
                if (!(ds.Tables[0].Rows[i]["incident_GPS"] is DBNull))
                {
                    newCaution.incidentGPS = (string)ds.Tables[0].Rows[i]["incident_GPS"];
                }

                //Initialise the combo boxes on the Caution form
                //and set to default values
                newCaution.initCode();
                if (newCaution.caution_code.Equals("C1"))
                {
                    newCaution.initC1Code();
                    newCaution.cautionC1CodeCombo.Enabled = true;
                }
                else
                {
                    newCaution.initC1Code();
                    newCaution.cautionC1CodeCombo.Enabled = false;
                }

                //Add the new Caution to global list of cautions
                cautions.Add(newCaution);
            }

            //Disconnect from the database
            closeDatabaseConnection();
        }

        //Method to get hold of details of all Sending Offs from database
        void getSendOffDetails(String connection_string)
        {
            //Connect to the database
            connectToDatabase(connection_string);

            //Pull out all Sending Off data and stick it in a DataSet
            string command = @"SELECT * FROM sendoff";
            SqlCeDataAdapter da = new SqlCeDataAdapter(command, conn);
            DataSet ds = new DataSet();
            da.Fill(ds, "sendoff_details");
            DataTable dt = ds.Tables["sendoff_details"];

            //Count the number of Sending Offs in the DataSet
            int numOfSendoffs = ds.Tables[0].Rows.Count;

            //Create a new Sending Off object
            SendOff newSendOff;
            for (int i = 0; i < numOfSendoffs; i++)
            {
                //Initialise the new Sending Off object
                newSendOff = new SendOff();

                //Set all details for the new Sending Off object
                newSendOff.match_id = (int)ds.Tables[0].Rows[i]["match_id"];
                newSendOff.sendoff_id = (int)ds.Tables[0].Rows[i]["sendoff_id"];
                newSendOff.sendoff_code = (string)ds.Tables[0].Rows[i]["sendoff_code"];
                newSendOff.player_id = (int)ds.Tables[0].Rows[i]["player_id"];
                newSendOff.player_name = getPlayerName(newSendOff.player_id, conn);
                newSendOff.club = getClubName(newSendOff.player_id, conn);
                newSendOff.time = (string)ds.Tables[0].Rows[i]["time"];
                if (!(ds.Tables[0].Rows[i]["whistle_GPS"] is DBNull))
                {
                    newSendOff.whistleGPS = (string)ds.Tables[0].Rows[i]["whistle_GPS"];
                }
                if (!(ds.Tables[0].Rows[i]["incident_GPS"] is DBNull))
                {
                    newSendOff.incidentGPS = (string)ds.Tables[0].Rows[i]["incident_GPS"];
                }

                //Initialise combo box on Sending Off form
                newSendOff.initCode();

                //Add the new Sending Off to the global list of Sending Offs
                sendoffs.Add(newSendOff);
            }            

            //Disconnect from the database
            closeDatabaseConnection();
        }

        //Method used to get hold of details of the Referee from the database
        public void getRefereeDetails(String connection_string)
        {
            //Connect to the database
            connectToDatabase(connection_string);

            //Pull out Referee data and stick it in a DataSet
            string command = @"SELECT * FROM referee";
            SqlCeDataAdapter da = new SqlCeDataAdapter(command, conn);
            DataSet ds = new DataSet();
            da.Fill(ds, "referee_details");
            DataTable dt = ds.Tables["referee_details"];
            
            //Set the fields within the RefereeProfile form with details from DataSet
            prof.refProfFirstnameTxt.Text = (string)ds.Tables[0].Rows[0]["forename"];
            prof.refProfSurnameTxt.Text = (string)ds.Tables[0].Rows[0]["surname"];
            prof.refProfNPDTxt.Text = (string)ds.Tables[0].Rows[0]["NPD_number"];
            prof.refProfFAMOATxt.Text = (string)ds.Tables[0].Rows[0]["FAMOA_number"];
            prof.refProfAddressTxt.Text = (string)ds.Tables[0].Rows[0]["address"];
            
            //Disconnect from the database
            closeDatabaseConnection();
        }

        //Method used to get hold of all Players associated with a specified Match
        void getMatchPlayers(int homeId, int awayId, Match theMatch)
        {
            //id of home and away team
            int home = homeId;
            int away = awayId;

            //The relevant Match object
            Match thisMatch = theMatch;

            //Count how many players exist in the system in total
            int size = players.Count;

            //Loop through the global list of all Players
            for (int i = 0; i < size; i++)
            {
                //If the player plays for the Matches home team
                //add them to the list of Players for the Match
                if (players[i].club_id == homeId)
                {
                    thisMatch.matchHomePlayers.Add(players[i]);
                }
                //Or if the player plays for the Matches away team,
                //also add them to the list of Match players
                else if (players[i].club_id == awayId)
                {
                    thisMatch.matchAwayPlayers.Add(players[i]);
                }
            }
        }

        //Method used to create all incidents to be displayed on the
        //Virtual Pitch for a specified Match
        void createVPIncidents(Match theMatch)
        {
            //Create a new incident item
            VirtualPitchIncident newVPItem;

            //Count the number of Caution and Sending Off
            //incidents in the specified Match
            int cautionCount = theMatch.matchCautions.Count;
            int sendOffCount = theMatch.matchSendOffs.Count;

            //Loop through all the Cautions in the Match
            for (int i = 0; i < cautionCount; i++)
            {
                //Variables to hold details of the Caution incident
                String vpDistanceStringC;
                String vpCautionStringC;
                String vpWhistleGPSStringC;
                String vpIncidentGPSStringC;
                String vpPlayerStringC;
                String vpTimeStringC;
                int vpIdC = theMatch.matchCautions[i].caution_id;

                //If the Caution has a code of 'C1', set variable showing incident details
                if (theMatch.matchCautions[i].caution_code.Equals("C1"))
                {
                    String c1 = theMatch.matchCautions[i].c1_code;
                    vpCautionStringC = "Caution, (C1, " + c1 + ")";
                }
                //Set incident details variable for all other types of Caution
                else
                {
                    String cCode = theMatch.matchCautions[i].caution_code;
                    vpCautionStringC = "Caution, (" + cCode + ")";
                }

                //Set GPS variables for the incident
                vpWhistleGPSStringC = theMatch.matchCautions[i].whistleGPS;
                vpIncidentGPSStringC = theMatch.matchCautions[i].incidentGPS;
                
                //Check that a GPS sentence exists for both whistle and incident
                if (!(vpWhistleGPSStringC.Equals("")) && !(vpIncidentGPSStringC.Equals("")))
                {
                    //Calculate distance between whistle and incident, and set relevant variable
                    string dist = hav.getDistance(vpWhistleGPSStringC, vpIncidentGPSStringC);
                    vpDistanceStringC = dist;

                    //Set Player and time details for the incident
                    vpPlayerStringC = theMatch.matchCautions[i].player_name + ", " + theMatch.matchCautions[i].club;
                    vpTimeStringC = theMatch.matchCautions[i].time;

                    //Obtain X,Y Point representing whistle and incident GPS locations
                    //for use when displaying incident on Virtual Pitch
                    Point whistlePoint = theMatch.parse(vpWhistleGPSStringC);
                    Point incidentPoint = theMatch.parse(vpIncidentGPSStringC);

                    //Create the new VirtualPitchIncident using all the newly specified variables
                    newVPItem = new VirtualPitchIncident(vpDistanceStringC, vpCautionStringC,
                        vpPlayerStringC, vpTimeStringC, vpWhistleGPSStringC, vpIncidentGPSStringC,
                        whistlePoint, incidentPoint, vpIdC);
                    
                    //Add the new incident to the list of incidents within the
                    //specified Match object
                    theMatch.vpDetails.Add(newVPItem);
                }
            }

            for (int i = 0; i < sendOffCount; i++)
            {
                String vpDistanceStringS;
                String vpSendOffStringS;
                String vpWhistleGPSStringS;
                String vpIncidentGPSStringS;
                String vpPlayerStringS;
                String vpTimeStringS;
                int vpIdS = theMatch.matchSendOffs[i].sendoff_id;

                String sCode = theMatch.matchSendOffs[i].sendoff_code;
                vpSendOffStringS = "Send Off, (" + sCode + ")";
                
                vpWhistleGPSStringS = theMatch.matchSendOffs[i].whistleGPS;
                vpIncidentGPSStringS = theMatch.matchSendOffs[i].incidentGPS;
                String dist = hav.getDistance(vpWhistleGPSStringS, vpIncidentGPSStringS);
                vpDistanceStringS = dist;
                vpPlayerStringS = theMatch.matchSendOffs[i].player_name + ", " + theMatch.matchSendOffs[i].club;
                vpTimeStringS = theMatch.matchSendOffs[i].time;

                Point whistlePoint = theMatch.parse(vpWhistleGPSStringS);
                Point incidentPoint = theMatch.parse(vpIncidentGPSStringS);

                newVPItem = new VirtualPitchIncident(vpDistanceStringS, vpSendOffStringS,
                    vpPlayerStringS, vpTimeStringS, vpWhistleGPSStringS, vpIncidentGPSStringS,
                    whistlePoint, incidentPoint, vpIdS);

                theMatch.vpDetails.Add(newVPItem);
            }
        }

        //method used to colour the list of Matches in this main RefOff screen
        void setListViewColors()
        {
            //Loop through all items in the List of Matches
            //and colour each alternate item Blue or White
            for (int i = 0; i < refOffLst.Items.Count; i++)
            {
                if (i % 2 == 0)
                {
                    ListViewItem nextItem = refOffLst.Items[i];
                    nextItem.BackColor = Color.SkyBlue;
                }
            } 
        }

        //Method called when the user clicks on a Match in the list displayed on
        //this main RefOff screen
        private void refOffLst_DoubleClick(object sender, EventArgs e)
        {   	
            //Get index of item that was clicked and locate the
            //relevant Match in the global list
            int ind = refOffLst.SelectedItems[0].Index;
            matches[ind].referrer = this;

            //Disable this screen and display the relevant Match Details screen
            this.Enabled = false;
            matches[ind].Visible = true;
        }

        //Method used to connect to the database
        private void connectToDatabase(String connection_string)
        {
            // Retrieve the connection string
            conString = connection_string;

            // Open the connection using the connection string
            conn = new SqlCeConnection(conString);
            conn.Open();
        }

        //Method to disconnect from the database
        private void closeDatabaseConnection()
        {
            //Close the database connection
            conn.Close();
        }

        //Method called when the 'Import Data' button is clicked
        //
        //This method uses functions from the OpenNETCF.Desktop.Communication DLL
        //which is freely available at the following location:
        //
        //http://www.opennetcf.com/FreeSoftware/DesktopCommunication/tabid/90/Default.aspx
        //
        //Note: This library has not been modified in any way
        private void refOffImportBtn_Click(object sender, EventArgs e)
        {
            //Create a new Microsoft Remote API object
            using (RAPI rapi = new RAPI())
            {      
                //Connect to the PDA device. This will display
                //an error message to the user if a device is not connected
                if (!(rapi.DevicePresent))
                {
                    MessageBox.Show("Please connect device");
                }
                else
                {
                    //Display the progress bar to the user
                    progress.Visible = true;

                    rapi.Connect(true);

                    //Incrememnt the progress bar
                    progress.progressProgressBar.PerformStep();

                    //Obtain the folder into which data is to be imported,
                    //as specified by the user in the ImportLocation form
                    String importDirectory = importLocation.directory;
                    progress.progressProgressBar.PerformStep();

                    //Copy the PDA database to the specified location on
                    //the desktop machine
                    rapi.CopyFileFromDevice(importDirectory + "\\RefOffPDA_DB.sdf", @"\\Program Files\\RefOffPDA\\RefOffPDA_DB.sdf");
                    progress.progressProgressBar.PerformStep();

                    //Set the connection string for the newly copied PDA database file
                    //on the desktop machine
                    pda_connection = "Data Source=" + importDirectory + "\\RefOffPDA_DB.sdf";
                    progress.progressProgressBar.PerformStep();

                    //Disconnect from the PDA device
                    rapi.Disconnect();
                    progress.progressProgressBar.PerformStep();

                    //Get hold of all the data from the PDA database file
                    getPDAData(pda_connection);
                    progress.progressProgressBar.PerformStep();

                    //Import all data taken out of PDA database file into
                    //the desktop database
                    importPDAData();
                    progress.progressProgressBar.PerformStep();

                    //Delete PDA file off desktop machine now data has been
                    //extracted from it
                    File.Delete(importDirectory + "\\RefOffPDA_DB.sdf");
                    progress.progressProgressBar.Value = 100;

                    //Hide the progress bar and reset it
                    progress.Visible = false;
                    progress.progressProgressBar.Value = 0;
                }
            }
        }

        //Method used to extract data from a PDA database file that has been
        //copied from a mobile device to the desktop machine this application is
        //running on
        private void getPDAData(String connection_string)
        {
            //Connect to the PDA database file
            connectToDatabase(connection_string);

            //Pull out match data and stick it in a DataSet
            string match_command = @"SELECT * FROM match";
            SqlCeDataAdapter match_da = new SqlCeDataAdapter(match_command, conn);
            match_ds = new DataSet();
            match_da.Fill(match_ds, "match_details");
            DataTable match_dt = match_ds.Tables["match_details"];

            //Pull out club data and stick it in a DataSet
            string club_command = @"SELECT * FROM club";
            SqlCeDataAdapter club_da = new SqlCeDataAdapter(club_command, conn);
            club_ds = new DataSet();
            club_da.Fill(club_ds, "club_details");
            DataTable club_dt = club_ds.Tables["club_details"];

            //Pull out player data and stick it in a DataSet
            string player_command = @"SELECT * FROM player";
            SqlCeDataAdapter player_da = new SqlCeDataAdapter(player_command, conn);
            player_ds = new DataSet();
            player_da.Fill(player_ds, "player_details");
            DataTable player_dt = player_ds.Tables["player_details"];

            //Pull out caution data and stick it in a DataSet
            string caution_command = @"SELECT * FROM caution";
            SqlCeDataAdapter caution_da = new SqlCeDataAdapter(caution_command, conn);
            caution_ds = new DataSet();
            caution_da.Fill(caution_ds, "caution_details");
            DataTable caution_dt = caution_ds.Tables["caution_details"];

            //Pull out sendoff data and stick it in a DataSet
            string sendoff_command = @"SELECT * FROM sendoff";
            SqlCeDataAdapter sendoff_da = new SqlCeDataAdapter(sendoff_command, conn);
            sendoff_ds = new DataSet();
            sendoff_da.Fill(sendoff_ds, "sendoff_details");
            DataTable sendoff_dt = sendoff_ds.Tables["sendoff_details"];

            //Pull out goal data and stick it in a DataSet
            string goal_command = @"SELECT * FROM goal";
            SqlCeDataAdapter goal_da = new SqlCeDataAdapter(goal_command, conn);
            goal_ds = new DataSet();
            goal_da.Fill(goal_ds, "goal_details");
            DataTable goal_dt = goal_ds.Tables["goal_details"];

            //Disconnect from the PDA database file
            closeDatabaseConnection();
        }              

        //Method used to update data from DataSets created from PDA database file data,
        //and then import it into the desktop application database
        private void importPDAData()
        {
            //Count the number of items in each of the DataSets
            numOfImportedMatches = match_ds.Tables[0].Rows.Count;
            numOfImportedClubs = club_ds.Tables[0].Rows.Count;
            numOfImportedPlayers = player_ds.Tables[0].Rows.Count;
            numOfImportedCautions = caution_ds.Tables[0].Rows.Count;
            numOfImportedSendoffs = sendoff_ds.Tables[0].Rows.Count;
            numOfImportedGoals = goal_ds.Tables[0].Rows.Count;

            //Create temporary DataSets used while processing imported data

            //Setup another temporary DataSet with the required fields
            //for a Match database record
            DataSet tempMatch_ds = new DataSet();
            tempMatch_ds.Tables.Add();
            tempMatch_ds.Tables[0].Columns.Add("match_id");
            tempMatch_ds.Tables[0].Columns.Add("home_club_id");
            tempMatch_ds.Tables[0].Columns.Add("away_club_id");
            tempMatch_ds.Tables[0].Columns.Add("competition");
            tempMatch_ds.Tables[0].Columns.Add("football_association");
            tempMatch_ds.Tables[0].Columns.Add("date");

            //Setup another temporary DataSet with the required fields
            //for a Club database record
            DataSet tempClub_ds = new DataSet();
            tempClub_ds.Tables.Add();
            tempClub_ds.Tables[0].Columns.Add("club_id");
            tempClub_ds.Tables[0].Columns.Add("name");
            tempClub_ds.Tables[0].Columns.Add("team");

            //Setup another temporary DataSet with the required fields
            //for a Player database record
            DataSet tempPlayer_ds = new DataSet();
            tempPlayer_ds.Tables.Add();
            tempPlayer_ds.Tables[0].Columns.Add("player_id");
            tempPlayer_ds.Tables[0].Columns.Add("surname");
            tempPlayer_ds.Tables[0].Columns.Add("on_pitch");
            tempPlayer_ds.Tables[0].Columns.Add("caution_history");
            tempPlayer_ds.Tables[0].Columns.Add("number");
            tempPlayer_ds.Tables[0].Columns.Add("club_id");
            tempPlayer_ds.Tables[0].Columns.Add("substituted");
            tempPlayer_ds.Tables[0].Columns.Add("reset_value");
            tempPlayer_ds.Tables[0].Columns.Add("forename");

            //Setup another temporary DataSet with the required fields
            //for a Caution database record
            DataSet tempCaution_ds = new DataSet();
            tempCaution_ds.Tables.Add();
            tempCaution_ds.Tables[0].Columns.Add("caution_id");
            tempCaution_ds.Tables[0].Columns.Add("caution_code");
            tempCaution_ds.Tables[0].Columns.Add("c1_code");
            tempCaution_ds.Tables[0].Columns.Add("time");
            tempCaution_ds.Tables[0].Columns.Add("player_id");
            tempCaution_ds.Tables[0].Columns.Add("match_id");
            tempCaution_ds.Tables[0].Columns.Add("whistle_GPS");
            tempCaution_ds.Tables[0].Columns.Add("incident_GPS");

            //Setup another temporary DataSet with the required fields
            //for a Sending Off database record
            DataSet tempSendoff_ds = new DataSet();
            tempSendoff_ds.Tables.Add();
            tempSendoff_ds.Tables[0].Columns.Add("sendoff_id");
            tempSendoff_ds.Tables[0].Columns.Add("sendoff_code");
            tempSendoff_ds.Tables[0].Columns.Add("time");
            tempSendoff_ds.Tables[0].Columns.Add("player_id");
            tempSendoff_ds.Tables[0].Columns.Add("match_id");
            tempSendoff_ds.Tables[0].Columns.Add("whistle_GPS");
            tempSendoff_ds.Tables[0].Columns.Add("incident_GPS");

            //Setup another temporary DataSet with the required fields
            //for a Goal database record
            DataSet tempGoal_ds = new DataSet();
            tempGoal_ds.Tables.Add();
            tempGoal_ds.Tables[0].Columns.Add("goal_id");
            tempGoal_ds.Tables[0].Columns.Add("player_id");
            tempGoal_ds.Tables[0].Columns.Add("match_id");
            tempGoal_ds.Tables[0].Columns.Add("time");
            tempGoal_ds.Tables[0].Columns.Add("own_goal");

            //Create new Row items to be added to the tables in the temporary DataSets
            DataRow match;
            DataRow club;
            DataRow player;
            DataRow caution;
            DataRow sendoff;
            DataRow goal;

            //Loop through each of the matches imported from the PDA device and populate
            //the temporary DataSets, update foreign key values, and insert data into
            //desktop application database
            for (int i = 0; i < numOfImportedMatches; i++)
            {
                //Add match details to temp match DataSet
                match = tempMatch_ds.Tables[0].NewRow();
                match["match_id"] = match_ds.Tables[0].Rows[i]["match_id"];
                match["home_club_id"] = match_ds.Tables[0].Rows[i]["home_club_id"];
                match["away_club_id"] = match_ds.Tables[0].Rows[i]["away_club_id"];
                match["competition"] = match_ds.Tables[0].Rows[i]["competition"];
                match["football_association"] = match_ds.Tables[0].Rows[i]["football_association"];
                match["date"] = match_ds.Tables[0].Rows[i]["date"];
                tempMatch_ds.Tables[0].Rows.Add(match);
                           
                //Loop through all cautions for this match
                for (int j = 0; j < numOfImportedCautions; j++)
                {
                    //Add caution details to temp caution DataSet                    
                    if (caution_ds.Tables[0].Rows[j]["match_id"].ToString().Equals(match["match_id"].ToString()))
                    {
                        caution = tempCaution_ds.Tables[0].NewRow();
                        caution["caution_id"] = caution_ds.Tables[0].Rows[j]["caution_id"];
                        caution["caution_code"] = caution_ds.Tables[0].Rows[j]["caution_code"];
                        caution["c1_code"] = caution_ds.Tables[0].Rows[j]["c1_code"];
                        caution["time"] = caution_ds.Tables[0].Rows[j]["time"];
                        caution["player_id"] = caution_ds.Tables[0].Rows[j]["player_id"];
                        caution["match_id"] = caution_ds.Tables[0].Rows[j]["match_id"];
                        caution["whistle_GPS"] = caution_ds.Tables[0].Rows[j]["whistle_GPS"];
                        caution["incident_GPS"] = caution_ds.Tables[0].Rows[j]["incident_GPS"];
                        tempCaution_ds.Tables[0].Rows.Add(caution);                        
                    }
                }

                //Loop through all Sending Offs for this match
                for (int k = 0; k < numOfImportedSendoffs; k++)
                {
                    //Add Sending Off details to temp sendoff DataSet
                    if (sendoff_ds.Tables[0].Rows[k]["match_id"].ToString().Equals(match["match_id"].ToString()))
                    {
                        sendoff = tempSendoff_ds.Tables[0].NewRow();
                        sendoff["sendoff_id"] = sendoff_ds.Tables[0].Rows[k]["sendoff_id"];
                        sendoff["sendoff_code"] = sendoff_ds.Tables[0].Rows[k]["sendoff_code"];
                        sendoff["time"] = sendoff_ds.Tables[0].Rows[k]["time"];
                        sendoff["player_id"] = sendoff_ds.Tables[0].Rows[k]["player_id"];
                        sendoff["match_id"] = sendoff_ds.Tables[0].Rows[k]["match_id"];
                        sendoff["whistle_GPS"] = sendoff_ds.Tables[0].Rows[k]["whistle_GPS"];
                        sendoff["incident_GPS"] = sendoff_ds.Tables[0].Rows[k]["incident_GPS"];
                        tempSendoff_ds.Tables[0].Rows.Add(sendoff);
                    }
                }

                //Loop through all goals for this match
                for (int l = 0; l < numOfImportedGoals; l++)
                {
                    //Add goal details to temp goal DataSet
                    if (goal_ds.Tables[0].Rows[l]["match_id"].ToString().Equals(match["match_id"].ToString()))
                    {
                        goal = tempGoal_ds.Tables[0].NewRow();
                        goal["goal_id"] = goal_ds.Tables[0].Rows[l]["goal_id"];                        
                        goal["player_id"] = goal_ds.Tables[0].Rows[l]["player_id"];
                        goal["match_id"] = goal_ds.Tables[0].Rows[l]["match_id"];
                        goal["time"] = goal_ds.Tables[0].Rows[l]["time"];
                        goal["own_goal"] = goal_ds.Tables[0].Rows[l]["own_goal"];
                        tempGoal_ds.Tables[0].Rows.Add(goal);
                    }
                }

                //Obtain id of home and away club from temp match DataSet
                int homeClubId = Int32.Parse(match["home_club_id"].ToString());
                int awayClubId = Int32.Parse(match["away_club_id"].ToString());

                //Loop through all imported clubs to find the two involved in
                //this match
                for (int m = 0; m < numOfImportedClubs; m++)
                {
                    //Enter club details into temp club DataSet for this match
                    if(Int32.Parse(club_ds.Tables[0].Rows[m]["club_id"].ToString()) == homeClubId)
                    {
                        club = tempClub_ds.Tables[0].NewRow();
                        club["club_id"] = club_ds.Tables[0].Rows[m]["club_id"];
                        club["name"] = club_ds.Tables[0].Rows[m]["name"];
                        club["team"] = club_ds.Tables[0].Rows[m]["team"];
                        tempClub_ds.Tables[0].Rows.Add(club);
                    }
                    else if (Int32.Parse(club_ds.Tables[0].Rows[m]["club_id"].ToString()) == awayClubId)
                    {
                        club = tempClub_ds.Tables[0].NewRow();
                        club["club_id"] = club_ds.Tables[0].Rows[m]["club_id"];
                        club["name"] = club_ds.Tables[0].Rows[m]["name"];
                        club["team"] = club_ds.Tables[0].Rows[m]["team"];
                        tempClub_ds.Tables[0].Rows.Add(club);
                    }
                }
                
                //Loop through all imported players to find those involved in this match
                for (int n = 0; n < numOfImportedPlayers; n++)
                {
                    //Enter details of this matches players into temp player DataSet
                    if (Int32.Parse(player_ds.Tables[0].Rows[n]["club_id"].ToString()) == homeClubId)
                    {
                        player = tempPlayer_ds.Tables[0].NewRow();
                        player["player_id"] = player_ds.Tables[0].Rows[n]["player_id"];
                        player["surname"] = player_ds.Tables[0].Rows[n]["surname"];
                        player["on_pitch"] = player_ds.Tables[0].Rows[n]["on_pitch"];
                        player["caution_history"] = player_ds.Tables[0].Rows[n]["caution_history"];
                        player["number"] = player_ds.Tables[0].Rows[n]["number"];
                        player["club_id"] = player_ds.Tables[0].Rows[n]["club_id"];
                        player["substituted"] = player_ds.Tables[0].Rows[n]["substituted"];
                        player["reset_value"] = player_ds.Tables[0].Rows[n]["reset_value"];
                        player["forename"] = player_ds.Tables[0].Rows[n]["forename"];
                        tempPlayer_ds.Tables[0].Rows.Add(player);
                    }
                    else if (Int32.Parse(player_ds.Tables[0].Rows[n]["club_id"].ToString()) == awayClubId)
                    {
                        player = tempPlayer_ds.Tables[0].NewRow();
                        player["player_id"] = player_ds.Tables[0].Rows[n]["player_id"];
                        player["surname"] = player_ds.Tables[0].Rows[n]["surname"];
                        player["on_pitch"] = player_ds.Tables[0].Rows[n]["on_pitch"];
                        player["caution_history"] = player_ds.Tables[0].Rows[n]["caution_history"];
                        player["number"] = player_ds.Tables[0].Rows[n]["number"];
                        player["club_id"] = player_ds.Tables[0].Rows[n]["club_id"];
                        player["substituted"] = player_ds.Tables[0].Rows[n]["substituted"];
                        player["reset_value"] = player_ds.Tables[0].Rows[n]["reset_value"];
                        player["forename"] = player_ds.Tables[0].Rows[n]["forename"];
                        tempPlayer_ds.Tables[0].Rows.Add(player);
                    }
                }

                //Count how many items exist in each of the temporary DataSets
                int numOfTempMatches = numOfImportedMatches;
                int numOfTempCautions = numOfImportedCautions;
                int numOfTempSendoffs = numOfImportedSendoffs;
                int numOfTempPlayers = numOfImportedPlayers;
                int numOfTempClubs = numOfImportedClubs;
                int numOfTempGoals = numOfImportedGoals;

                //Update foreign key value 'match_id' for all cautions
                for (int o = 0; o < numOfTempCautions; o++)
                {
                    tempCaution_ds.Tables[0].Rows[o]["match_id"] = nextMatchId;
                }

                //Update foreign key value 'match_id' for all sending offs
                for (int p = 0; p < numOfTempSendoffs; p++)
                {
                    tempSendoff_ds.Tables[0].Rows[p]["match_id"] = nextMatchId;
                }

                //Update foreign key value 'match_id' for all goals
                for (int q = 0; q < numOfTempGoals; q++)
                {
                    tempGoal_ds.Tables[0].Rows[q]["match_id"] = nextMatchId;
                }

                //Update foreign key value 'club_id' for all players
                for (int r = 0; r < numOfTempPlayers; r++)
                {
                    int thisPlayerId = Int32.Parse(tempPlayer_ds.Tables[0].Rows[r]["player_id"].ToString());

                    if (Int32.Parse(tempPlayer_ds.Tables[0].Rows[r]["club_id"].ToString()) == homeClubId)
                    {
                        tempPlayer_ds.Tables[0].Rows[r]["club_id"] = nextClubId;
                    }
                    else
                    {
                        tempPlayer_ds.Tables[0].Rows[r]["club_id"] = nextClubId + 1;
                    }

                    //Update foreign key 'player_id' for all goals
                    for (int s = 0; s < numOfTempGoals; s++)
                    {                        
                        if (Int32.Parse(tempGoal_ds.Tables[0].Rows[s]["player_id"].ToString()) == thisPlayerId)
                        {
                            tempGoal_ds.Tables[0].Rows[s]["player_id"] = nextPlayerId;
                        }
                    }

                    //Update foreign key 'player_id' for all sending offs
                    for (int t = 0; t < numOfTempSendoffs; t++)
                    {                        
                        if (Int32.Parse(tempSendoff_ds.Tables[0].Rows[t]["player_id"].ToString()) ==thisPlayerId)
                        {
                            tempSendoff_ds.Tables[0].Rows[t]["player_id"] = nextPlayerId;
                        }
                    }

                    //Update foreign key 'player_id' for all cautions
                    for (int u = 0; u < numOfTempCautions; u++)
                    {
                        if (Int32.Parse(tempCaution_ds.Tables[0].Rows[u]["player_id"].ToString()) == thisPlayerId)
                        {
                            tempCaution_ds.Tables[0].Rows[u]["player_id"] = nextPlayerId;
                        }
                    }
                    nextPlayerId++;
                }

                //Update club id foreign keys within the match details
                match["home_club_id"] = nextClubId;
                nextClubId++;
                match["away_club_id"] = nextClubId;
                nextClubId++;

                //Connect to the database ready to enter the new data
                connectToDatabase(desktop_connection);

                //Create SQL commands to insert imported data into desktop database
                string match_import_command = @"INSERT INTO match(home_club_id,away_club_id,competition,football_association,date) VALUES(@home_club_id,@away_club_id,@competition,@football_association,@date);";
                string caution_import_command = @"INSERT INTO caution(caution_code,c1_code,time,player_id,match_id,whistle_GPS,incident_GPS) VALUES(@caution_code,@c1_code,@time,@player_id,@match_id,@whistle_GPS,@incident_GPS);";
                string sendoff_import_command = @"INSERT INTO sendoff(sendoff_code,time,player_id,match_id,whistle_GPS,incident_GPS) VALUES(@sendoff_code,@time,@player_id,@match_id,@whistle_GPS,@incident_GPS);";
                string goal_import_command = @"INSERT INTO goal(player_id,match_id,time,own_goal) VALUES(@player_id,@match_id,@time,@own_goal);";
                string club_import_command = @"INSERT INTO club(name,team) VALUES(@name,@team);";
                string player_import_command = @"INSERT INTO player(surname,on_pitch,number,club_id,substituted,reset_value,forename) VALUES(@surname,@on_pitch,@number,@club_id,@substituted,@reset_value,@forename);";

                //Execute SQL command to insert new club details into desktop database
                for (int y = 0; y < numOfTempClubs; y++)
                {                    
                    using (SqlCeCommand club_import = new SqlCeCommand(club_import_command, conn))
                    {
                        club_import.Parameters.AddWithValue("@name", (string)tempClub_ds.Tables[0].Rows[y]["name"]);
                        club_import.Parameters.AddWithValue("@team", (string)tempClub_ds.Tables[0].Rows[y]["team"]);
                        club_import.ExecuteNonQuery();                        
                    }
                }

                //Execute SQL command to insert new match details into desktop database
                using (SqlCeCommand match_import = new SqlCeCommand(match_import_command, conn))
                {
                    match_import.Parameters.AddWithValue("@home_club_id", Int32.Parse(tempMatch_ds.Tables[0].Rows[0]["home_club_id"].ToString()));
                    match_import.Parameters.AddWithValue("@away_club_id", Int32.Parse(tempMatch_ds.Tables[0].Rows[0]["away_club_id"].ToString()));
                    match_import.Parameters.AddWithValue("@competition", (string)tempMatch_ds.Tables[0].Rows[0]["competition"]);
                    match_import.Parameters.AddWithValue("@football_association", (string)tempMatch_ds.Tables[0].Rows[0]["football_association"]);
                    match_import.Parameters.AddWithValue("@date", (string)tempMatch_ds.Tables[0].Rows[0]["date"]);
                    match_import.ExecuteNonQuery();
                }

                //Execute SQL command to insert new player details into desktop database
                for (int z = 0; z < numOfTempPlayers; z++)
                {
                    using (SqlCeCommand player_import = new SqlCeCommand(player_import_command, conn))
                    {
                        player_import.Parameters.AddWithValue("@surname", (string)tempPlayer_ds.Tables[0].Rows[z]["surname"]);
                        player_import.Parameters.AddWithValue("@on_pitch", Int32.Parse(tempPlayer_ds.Tables[0].Rows[z]["on_pitch"].ToString()));
                        //player_import.Parameters.AddWithValue("@caution_history", Int32.Parse(tempPlayer_ds.Tables[0].Rows[z]["caution_history"].ToString()));
                        player_import.Parameters.AddWithValue("@number", Int32.Parse(tempPlayer_ds.Tables[0].Rows[z]["number"].ToString()));
                        player_import.Parameters.AddWithValue("@club_id", Int32.Parse(tempPlayer_ds.Tables[0].Rows[z]["club_id"].ToString()));
                        player_import.Parameters.AddWithValue("@substituted", Int32.Parse(tempPlayer_ds.Tables[0].Rows[z]["substituted"].ToString()));
                        player_import.Parameters.AddWithValue("@reset_value", Int32.Parse(tempPlayer_ds.Tables[0].Rows[z]["reset_value"].ToString()));
                        player_import.Parameters.AddWithValue("@forename", (string)tempPlayer_ds.Tables[0].Rows[z]["forename"]);
                        player_import.ExecuteNonQuery();
                    }
                }

                //Execute SQL command to insert new caution details into desktop database
                for (int v = 0; v < numOfTempCautions; v++)
                {
                    using (SqlCeCommand caution_import = new SqlCeCommand(caution_import_command, conn))
                    {
                        caution_import.Parameters.AddWithValue("@caution_code", (string)tempCaution_ds.Tables[0].Rows[v]["caution_code"]);
                        if (!(tempCaution_ds.Tables[0].Rows[v]["c1_code"] is DBNull))
                        {
                            caution_import.Parameters.AddWithValue("@c1_code", (string)tempCaution_ds.Tables[0].Rows[v]["c1_code"]);
                        }
                        else
                        {
                            caution_import.Parameters.AddWithValue("@c1_code", tempCaution_ds.Tables[0].Rows[v]["c1_code"]);
                        }
                        caution_import.Parameters.AddWithValue("@time", (string)tempCaution_ds.Tables[0].Rows[v]["time"]);
                        caution_import.Parameters.AddWithValue("@player_id", Int32.Parse(tempCaution_ds.Tables[0].Rows[v]["player_id"].ToString()));
                        caution_import.Parameters.AddWithValue("@match_id", Int32.Parse(tempCaution_ds.Tables[0].Rows[v]["match_id"].ToString()));
                        if (!(tempCaution_ds.Tables[0].Rows[v]["whistle_GPS"] is DBNull))
                        {
                            caution_import.Parameters.AddWithValue("@whistle_GPS", (string)tempCaution_ds.Tables[0].Rows[v]["whistle_GPS"]);
                        }
                        else
                        {
                            caution_import.Parameters.AddWithValue("@whistle_GPS", tempCaution_ds.Tables[0].Rows[v]["whistle_GPS"]);
                        }
                        if (!(tempCaution_ds.Tables[0].Rows[v]["incident_GPS"] is DBNull))
                        {
                            caution_import.Parameters.AddWithValue("@incident_GPS", (string)tempCaution_ds.Tables[0].Rows[v]["incident_GPS"]);
                        }
                        else
                        {
                            caution_import.Parameters.AddWithValue("@incident_GPS", tempCaution_ds.Tables[0].Rows[v]["incident_GPS"]);
                        }
                        caution_import.ExecuteNonQuery();
                    }
                }

                //Execute SQL command to insert new sending off details into desktop database
                for (int w = 0; w < numOfTempSendoffs; w++)
                {
                    using (SqlCeCommand sendoff_import = new SqlCeCommand(sendoff_import_command, conn))
                    {
                        sendoff_import.Parameters.AddWithValue("@sendoff_code", (string)tempSendoff_ds.Tables[0].Rows[w]["sendoff_code"]);
                        sendoff_import.Parameters.AddWithValue("@time", (string)tempSendoff_ds.Tables[0].Rows[w]["time"]);
                        sendoff_import.Parameters.AddWithValue("@player_id", Int32.Parse(tempSendoff_ds.Tables[0].Rows[w]["player_id"].ToString()));
                        sendoff_import.Parameters.AddWithValue("@match_id", Int32.Parse(tempSendoff_ds.Tables[0].Rows[w]["match_id"].ToString()));
                        if (!(tempSendoff_ds.Tables[0].Rows[w]["whistle_GPS"] is DBNull))
                        {
                            sendoff_import.Parameters.AddWithValue("@whistle_GPS", (string)tempSendoff_ds.Tables[0].Rows[w]["whistle_GPS"]);
                        }
                        else
                        {
                            sendoff_import.Parameters.AddWithValue("@whistle_GPS", tempSendoff_ds.Tables[0].Rows[w]["whistle_GPS"]);
                        }
                        if (!(tempSendoff_ds.Tables[0].Rows[w]["incident_GPS"] is DBNull))
                        {
                            sendoff_import.Parameters.AddWithValue("@incident_GPS", (string)tempSendoff_ds.Tables[0].Rows[w]["incident_GPS"]);
                        }
                        else
                        {
                            sendoff_import.Parameters.AddWithValue("@incident_GPS", tempSendoff_ds.Tables[0].Rows[w]["incident_GPS"]);
                        }
                        sendoff_import.ExecuteNonQuery();
                    }
                }

                //Execute SQL command to insert new goal details into desktop database
                for (int x = 0; x < numOfTempGoals; x++)
                {
                    using (SqlCeCommand goal_import = new SqlCeCommand(goal_import_command, conn))
                    {
                        goal_import.Parameters.AddWithValue("@player_id", Int32.Parse(tempGoal_ds.Tables[0].Rows[x]["player_id"].ToString()));
                        goal_import.Parameters.AddWithValue("@match_id", Int32.Parse(tempGoal_ds.Tables[0].Rows[x]["match_id"].ToString()));
                        goal_import.Parameters.AddWithValue("@time", (string)tempGoal_ds.Tables[0].Rows[x]["time"]);
                        goal_import.Parameters.AddWithValue("@own_goal", Int32.Parse(tempGoal_ds.Tables[0].Rows[x]["own_goal"].ToString()));
                        goal_import.ExecuteNonQuery();
                    }
                }

                //Create temporary objects to allow imported data
                //to be displayed within this application
                Caution tempCaution;
                SendOff tempSendOff;
                Goal tempGoal;
                Player tempPlayer;
                Club tempClub;

                //Create all required Club objects
                for (int e = 0; e < tempClub_ds.Tables[0].Rows.Count; e++)
                {
                    tempClub = new Club();
                    tempClub.club_id = Int32.Parse(tempClub_ds.Tables[0].Rows[e]["club_id"].ToString());
                    tempClub.name = (string)tempClub_ds.Tables[0].Rows[e]["name"];
                    tempClub.team = (string)tempClub_ds.Tables[0].Rows[e]["team"];
                    clubs.Add(tempClub);
                }

                //Create all required Player objects
                for (int c = 0; c < tempPlayer_ds.Tables[0].Rows.Count; c++)
                {
                    tempPlayer = new Player();
                    tempPlayer.player_id = Int32.Parse(tempPlayer_ds.Tables[0].Rows[c]["player_id"].ToString());
                    tempPlayer.surname = (string)tempPlayer_ds.Tables[0].Rows[c]["surname"];
                    tempPlayer.forename = (string)tempPlayer_ds.Tables[0].Rows[c]["forename"];
                    tempPlayer.number = Int32.Parse(tempPlayer_ds.Tables[0].Rows[c]["number"].ToString());
                    tempPlayer.club_id = Int32.Parse(tempPlayer_ds.Tables[0].Rows[c]["club_id"].ToString());
                    players.Add(tempPlayer);
                }

                //Create all required Caution objects
                for (int a = 0; a < numOfTempCautions; a++)
                {
                    tempCaution = new Caution();
                    tempCaution.match_id = Int32.Parse(tempCaution_ds.Tables[0].Rows[a]["match_id"].ToString());
                    tempCaution.caution_id = Int32.Parse(tempCaution_ds.Tables[0].Rows[a]["caution_id"].ToString());
                    tempCaution.caution_code = (string)tempCaution_ds.Tables[0].Rows[a]["caution_code"];
                    if (tempCaution_ds.Tables[0].Rows[a]["c1_code"] is DBNull)
                    {
                        tempCaution.c1_code = "-";
                    }
                    else
                    {
                        tempCaution.c1_code = (string)tempCaution_ds.Tables[0].Rows[a]["c1_code"];
                    }
                    tempCaution.player_id = Int32.Parse(tempCaution_ds.Tables[0].Rows[a]["player_id"].ToString());
                    tempCaution.player_name = getPlayerName(tempCaution.player_id, conn);
                    tempCaution.player_surname = getPlayerSurname(tempCaution.player_id, conn);
                    tempCaution.player_firstname = getPlayerFirstname(tempCaution.player_id, conn);
                    tempCaution.club = getClubName(tempCaution.player_id, conn);
                    tempCaution.time = (string)tempCaution_ds.Tables[0].Rows[a]["time"];
                    if (!(tempCaution_ds.Tables[0].Rows[a]["whistle_GPS"] is DBNull))
                    {
                        tempCaution.whistleGPS = (string)tempCaution_ds.Tables[0].Rows[a]["whistle_GPS"];
                    }
                    if (!(tempCaution_ds.Tables[0].Rows[a]["incident_GPS"] is DBNull))
                    {
                        tempCaution.incidentGPS = (string)tempCaution_ds.Tables[0].Rows[a]["incident_GPS"];
                    }
                    tempCaution.initCode();
                    if (!(tempCaution.c1_code.Equals("-")))
                    {
                        tempCaution.initC1Code();
                    }
                    else
                    {
                        tempCaution.cautionC1CodeCombo.Enabled = false;
                    }
                    cautions.Add(tempCaution);
                }

                //Create all required Goal objects
                for (int b = 0; b < numOfTempGoals; b++)
                {
                    tempGoal = new Goal();
                    tempGoal.match_id = Int32.Parse(tempGoal_ds.Tables[0].Rows[b]["match_id"].ToString());
                    tempGoal.goal_id = Int32.Parse(tempGoal_ds.Tables[0].Rows[b]["goal_id"].ToString());
                    tempGoal.Tag = tempGoal.goal_id;
                    tempGoal.own_goal = Int32.Parse(tempGoal_ds.Tables[0].Rows[b]["own_goal"].ToString());
                    tempGoal.player_id = Int32.Parse(tempGoal_ds.Tables[0].Rows[b]["player_id"].ToString());
                    tempGoal.player_name = getPlayerName(tempGoal.player_id, conn);
                    tempGoal.time = (string)tempGoal_ds.Tables[0].Rows[b]["time"];
                    tempGoal.club = getClubName(tempGoal.player_id, conn);
                    tempGoal.clubId = getClubId(tempGoal.player_id, conn);
                    tempGoal.otherClubId = getOtherClubId(tempGoal.match_id, tempGoal.clubId, conn);
                    tempGoal.otherClub = getOtherClubName(tempGoal.otherClubId, conn);
                    for (int j = 0; j < players.Count; j++)
                    {
                        if (players[j].club_id == tempGoal.clubId)
                        {
                            tempGoal.goalMatchPlayers.Add(players[j]);
                        }
                        else if (players[j].club_id == tempGoal.otherClubId)
                        {
                            tempGoal.goalMatchPlayers.Add(players[j]);
                        }
                    }
                    tempGoal.goalClubCombo.Items.Add(tempGoal.club);
                    tempGoal.goalClubCombo.Items.Add(tempGoal.otherClub);
                    tempGoal.goalClubCombo.SelectedIndex = 0;
                    int index = 0;
                    int counter = 0;
                    String name;
                    for (int k = 0; k < tempGoal.goalMatchPlayers.Count; k++)
                    {
                        if (tempGoal.goalMatchPlayers[k].club_id == tempGoal.clubId)
                        {
                            if (tempGoal.goalMatchPlayers[k].player_id == tempGoal.player_id)
                            {
                                index = counter;
                            }
                            name = getPlayerName(tempGoal.goalMatchPlayers[k].player_id, conn);
                            tempGoal.goalPlayerCombo.Items.Add(name);
                            counter++;
                        }
                    }
                    tempGoal.goalPlayerCombo.SelectedIndex = index;
                    goals.Add(tempGoal);
                }

                //Create all required Sending Off objects
                for (int d = 0; d < tempSendoff_ds.Tables[0].Rows.Count; d++)
                {
                    tempSendOff = new SendOff();
                    tempSendOff.match_id = Int32.Parse(tempSendoff_ds.Tables[0].Rows[d]["match_id"].ToString());
                    tempSendOff.sendoff_id = Int32.Parse(tempSendoff_ds.Tables[0].Rows[d]["sendoff_id"].ToString());
                    tempSendOff.sendoff_code = (string)tempSendoff_ds.Tables[0].Rows[d]["sendoff_code"];
                    tempSendOff.player_id = Int32.Parse(tempSendoff_ds.Tables[0].Rows[d]["player_id"].ToString());
                    tempSendOff.player_name = getPlayerName(tempSendOff.player_id, conn);
                    tempSendOff.club = getClubName(tempSendOff.player_id, conn);
                    tempSendOff.time = (string)tempSendoff_ds.Tables[0].Rows[d]["time"];
                    if (!(tempSendoff_ds.Tables[0].Rows[d]["whistle_GPS"] is DBNull))
                    {
                        tempSendOff.whistleGPS = (string)tempSendoff_ds.Tables[0].Rows[d]["whistle_GPS"];
                    }
                    if (!(tempSendoff_ds.Tables[0].Rows[d]["incident_GPS"] is DBNull))
                    {
                        tempSendOff.incidentGPS = (string)tempSendoff_ds.Tables[0].Rows[d]["incident_GPS"];
                    }
                    tempSendOff.initCode();
                    sendoffs.Add(tempSendOff);
                }                

                //Disconnect from the database
                closeDatabaseConnection();                

                //Add the newly imported match to the list of matches
                //displayed on the main RefOff screen
                getNewMatch(desktop_connection, nextMatchId);
                
                //Refresh the list of displayed matches
                refOffLst.Refresh();
                this.setListViewColors();

                //Configure variables used during next import operation
                getMaximumDBValues(desktop_connection);

                //Clear temp datasets
                tempMatch_ds.Clear();
                tempCaution_ds.Clear();
                tempSendoff_ds.Clear();
                tempPlayer_ds.Clear();
                tempGoal_ds.Clear();
                tempClub_ds.Clear();                
            }
        }

        //Method used to obtain the next primary key values for all tables in the
        //database, for use when importing data
        public void getMaximumDBValues(String connection_string)
        {
            //Connect to the database
            connectToDatabase(connection_string);
                        
            //Create and execute SQL command to find maximum value of primary key
            //field in match table
            string matchId_command = @"SELECT max(match_id) FROM match";
            SqlCeDataAdapter matchId_da = new SqlCeDataAdapter(matchId_command, conn);
            DataSet matchId_ds = new DataSet();
            matchId_da.Fill(matchId_ds, "matchId_details");
            DataTable matchId_dt = matchId_ds.Tables["matchId_details"];

            //Set value of global variable indicating next available id
            int maxMatchId = (int)matchId_ds.Tables[0].Rows[0][0];
            nextMatchId = maxMatchId + 1;

            //Create and execute SQL command to find maximum value of primary key
            //field in club table
            string clubId_command = @"SELECT max(club_id) FROM club";
            SqlCeDataAdapter clubId_da = new SqlCeDataAdapter(clubId_command, conn);
            DataSet clubId_ds = new DataSet();
            clubId_da.Fill(clubId_ds, "clubId_details");
            DataTable clubId_dt = clubId_ds.Tables["clubId_details"];

            //Set value of global variable indicating next available id
            int maxClubId = (int)clubId_ds.Tables[0].Rows[0][0];
            nextClubId = maxClubId + 1;

            //Create and execute SQL command to find maximum value of primary key
            //field in player table
            string playerId_command = @"SELECT max(player_id) FROM player";
            SqlCeDataAdapter playerId_da = new SqlCeDataAdapter(playerId_command, conn);
            DataSet playerId_ds = new DataSet();
            playerId_da.Fill(playerId_ds, "playerId_details");
            DataTable playerId_dt = playerId_ds.Tables["playerId_details"];

            //Set value of global variable indicating next available id
            int maxPlayerId = (int)playerId_ds.Tables[0].Rows[0][0];
            nextPlayerId = maxPlayerId + 1;

            //Create and execute SQL command to find maximum value of primary key
            //field in caution table
            string cautionId_command = @"SELECT max(caution_id) FROM caution";
            SqlCeDataAdapter cautionId_da = new SqlCeDataAdapter(cautionId_command, conn);
            DataSet cautionId_ds = new DataSet();
            cautionId_da.Fill(cautionId_ds, "cautionId_details");
            DataTable cautionId_dt = cautionId_ds.Tables["cautionId_details"];

            //Set value of global variable indicating next available id
            int maxCautionId = (int)cautionId_ds.Tables[0].Rows[0][0];
            nextCautionId = maxCautionId + 1;

            //Create and execute SQL command to find maximum value of primary key
            //field in sendoff table
            string sendoffId_command = @"SELECT max(sendoff_id) FROM sendoff";
            SqlCeDataAdapter sendoffId_da = new SqlCeDataAdapter(sendoffId_command, conn);
            DataSet sendoffId_ds = new DataSet();
            sendoffId_da.Fill(sendoffId_ds, "sendoffId_details");
            DataTable sendoffId_dt = sendoffId_ds.Tables["sendoffId_details"];

            //Set value of global variable indicating next available id
            int maxSendoffId = (int)sendoffId_ds.Tables[0].Rows[0][0];
            nextSendoffId = maxSendoffId + 1;

            //Create and execute SQL command to find maximum value of primary key
            //field in goal table
            string goalId_command = @"SELECT max(goal_id) FROM goal";
            SqlCeDataAdapter goalId_da = new SqlCeDataAdapter(goalId_command, conn);
            DataSet goalId_ds = new DataSet();
            goalId_da.Fill(goalId_ds, "goalId_details");
            DataTable goalId_dt = goalId_ds.Tables["goalId_details"];

            //Set value of global variable indicating next available id
            int maxGoalId = (int)goalId_ds.Tables[0].Rows[0][0];
            nextGoalId = maxGoalId + 1;

            //Re-seed the identity fields/primary key fields within the database
            reseedDB(connection_string);
                        
            //Disconnect from the database
            closeDatabaseConnection();
        }

        //Method used to re-seed the identity/primary key fields within the
        //database for use during data import operation so the primary key
        //values for the imported data will be known
        private void reseedDB(String connection_string)
        {
            //Connect to the database
            connectToDatabase(connection_string);

            //Create and execute SQL command to re-seed identity field of match table to next
            //available primary key value
            string match_reseed_command = @"ALTER TABLE match ALTER COLUMN match_id IDENTITY(" + nextMatchId + ",1);";                        
            using (SqlCeCommand match_reseed = new SqlCeCommand(match_reseed_command, conn))
            {               
                match_reseed.ExecuteNonQuery();
            }

            //Create and execute SQL command to re-seed identity field of player table to next
            //available primary key value
            string player_reseed_command = @"ALTER TABLE player ALTER COLUMN player_id IDENTITY (" + nextPlayerId + ",1);";
            using (SqlCeCommand player_reseed = new SqlCeCommand(player_reseed_command, conn))
            {
                player_reseed.ExecuteNonQuery();
            }

            //Create and execute SQL command to re-seed identity field of club table to next
            //available primary key value
            string club_reseed_command = @"ALTER TABLE club ALTER COLUMN club_id IDENTITY (" + nextClubId + ",1);";
            using (SqlCeCommand club_reseed = new SqlCeCommand(club_reseed_command, conn))
            {
                club_reseed.ExecuteNonQuery();
            }

            //Create and execute SQL command to re-seed identity field of caution table to next
            //available primary key value
            string caution_reseed_command = @"ALTER TABLE caution ALTER COLUMN caution_id IDENTITY (" + nextCautionId + ",1);";
            using (SqlCeCommand caution_reseed = new SqlCeCommand(caution_reseed_command, conn))
            {
                caution_reseed.ExecuteNonQuery();
            }

            //Create and execute SQL command to re-seed identity field of sendoff table to next
            //available primary key value
            string sendoff_reseed_command = @"ALTER TABLE sendoff ALTER COLUMN sendoff_id IDENTITY (" + nextSendoffId + ",1);";
            using (SqlCeCommand sendoff_reseed = new SqlCeCommand(sendoff_reseed_command, conn))
            {
                sendoff_reseed.ExecuteNonQuery();
            }

            //Create and execute SQL command to re-seed identity field of goal table to next
            //available primary key value
            string goal_reseed_command = @"ALTER TABLE goal ALTER COLUMN goal_id IDENTITY (" + nextGoalId + ",1);";
            using (SqlCeCommand goal_reseed = new SqlCeCommand(goal_reseed_command, conn))
            {
                goal_reseed.ExecuteNonQuery();
            }

            closeDatabaseConnection();
        }

        //Method used to add a newly imported Match to the list of matches
        //displayed on the main RefOff screen
        void getNewMatch(String connection_string, int newMatchId)
        {
            //Connect to the database
            connectToDatabase(connection_string);

            //Pull out data for the specified match and stick it in a DataSet
            string command = @"SELECT match_id, home_club_id, away_club_id, competition, football_association, date FROM match WHERE match_id = " + newMatchId;
            SqlCeDataAdapter da = new SqlCeDataAdapter(command, conn);
            DataSet ds = new DataSet();
            da.Fill(ds, "match_details");
            DataTable dt = ds.Tables["match_details"];

            //There should only be one Match in the DataSet
            int numOfMatches = ds.Tables[0].Rows.Count;

            //Create the new Match item
            Match newMatch;

            //For the new Match...
            for (int i = 0; i < numOfMatches; i++)
            {
                //Initialise the new Match object
                newMatch = new Match();

                //Set all details for the new Match object based on values
                //taken from the database
                newMatch.match_id = (int)ds.Tables[0].Rows[i]["match_id"];
                if (!(ds.Tables[0].Rows[i]["date"] is DBNull))
                {
                    newMatch.db_date = (DateTime)ds.Tables[0].Rows[i]["date"];
                }
                newMatch.date = newMatch.db_date.ToString("d");
                newMatch.homeId = (int)ds.Tables[0].Rows[i]["home_club_id"];
                newMatch.homeTeam = getTeamDetails(newMatch.homeId, conn);
                newMatch.homeSquad = getSquadDetails(newMatch.homeId, conn);
                newMatch.awayId = (int)ds.Tables[0].Rows[i]["away_club_id"];
                newMatch.awayTeam = getTeamDetails(newMatch.awayId, conn);
                newMatch.awaySquad = getSquadDetails(newMatch.awayId, conn);
                newMatch.competition = (string)ds.Tables[0].Rows[i]["competition"];
                newMatch.association = (string)ds.Tables[0].Rows[i]["football_association"];
                
                //Add the new Match to the global list of matches
                matches.Add(newMatch);

                //Create and display all items associated with the Match
                getGoals(newMatch.match_id, conn);
                getCautions(newMatch.match_id, conn);
                getSendOffs(newMatch.match_id, conn);
                getMatchPlayers(newMatch.homeId, newMatch.awayId, newMatch);
                createVPIncidents(newMatch);
                newMatch.setListViewColors();

                //Create a new item to represent the Match within
                //the list on the main RefOff screen
                ListViewItem match;
                
                //Set details for the new Match item
                match = new ListViewItem();
                match.Text = newMatch.date;
                match.SubItems.Add(newMatch.homeTeam);
                match.SubItems.Add(newMatch.awayTeam);
                match.SubItems.Add(newMatch.competition);

                //Add the Match to the list displayed to the user
                refOffLst.Items.Add(match);
                matchesDisplayed++;              

                //Disconnect from the database
                closeDatabaseConnection();
            }
        }

        //Method run when the user selects the Referee Profile menu option
        private void profileToolStripMenuItem_Click(object sender, EventArgs e)
        {
            //Disable this screen and display the RefereeProfile form
            this.Enabled = false;
            prof.Visible = true;
        }

        //Method run when the user selects the Import Location menu item
        private void importLocationToolStripMenuItem_Click(object sender, EventArgs e)
        {
            //Disable this screen and display the ImportLocation form
            importLocation.Visible = true;
        }

        //Method run when the user clicks the 'Delete' button
        private void refOffDeleteBtn_Click(object sender, EventArgs e)
        {
            //Check a Match in the list has been selected
            if (refOffLst.SelectedItems.Count > 0)
            {
                //Get index of selected Match
                int matchIndex = refOffLst.SelectedItems[0].Index;

                //Get hold of Match and Club details
                Match tempMatch = matches[matchIndex];
                int tempMatchId = tempMatch.match_id;
                int homeClub = tempMatch.homeId;
                int awayClub = tempMatch.awayId;
                         
                //Delete all players from this Match
                for (int i = 0; i < players.Count; i++)
                {
                    if (players[i].club_id == homeClub || players[i].club_id == awayClub)
                    {
                        players.RemoveAt(i);
                    }
                }

                //Delete all clubs from this Match
                for (int j = 0; j < clubs.Count; j++)
                {
                    if (clubs[j].club_id == homeClub || clubs[j].club_id == awayClub)
                    {
                        clubs.RemoveAt(j);
                    }
                }

                //Delete all cautions from this Match
                for (int k = 0; k < cautions.Count; k++)
                {
                    if (cautions[k].match_id == tempMatchId || cautions[k].match_id == tempMatchId)
                    {
                        cautions.RemoveAt(k);
                    }
                }

                //Delete all sending offs from this Match
                for (int l = 0; l < sendoffs.Count; l++)
                {
                    if (sendoffs[l].match_id == tempMatchId || sendoffs[l].match_id == tempMatchId)
                    {
                        sendoffs.RemoveAt(l);
                    }
                }

                //Delete all goals from this Match
                for (int m = 0; m < goals.Count; m++)
                {
                    if (goals[m].match_id == tempMatchId || goals[m].match_id == tempMatchId)
                    {
                        goals.RemoveAt(m);
                    }
                }

                //Delete all Match details from database
                
                //Connect to the database
                connectToDatabase(desktop_connection);

                //Create SQL commands to delete data from database
                string match_delete_command = @"DELETE FROM match WHERE match_id = " + tempMatchId + ";";
                string caution_delete_command = @"DELETE FROM caution WHERE match_id = " + tempMatchId + ";";
                string sendoff_delete_command = @"DELETE FROM sendoff WHERE match_id = " + tempMatchId + ";";
                string goal_delete_command = @"DELETE FROM goal WHERE match_id = " + tempMatchId + ";";
                string home_club_delete_command = @"DELETE FROM club WHERE club_id = " + homeClub + ";";
                string away_club_delete_command = @"DELETE FROM club WHERE club_id = " + awayClub + ";";
                string home_player_delete_command = @"DELETE FROM player WHERE club_id = " + homeClub + ";";
                string away_player_delete_command = @"DELETE FROM player WHERE club_id = " + awayClub + ";";
                                
                //Execute SQL command to delete caution data from database                                  
                using (SqlCeCommand caution_delete = new SqlCeCommand(caution_delete_command, conn))
                {
                    caution_delete.ExecuteNonQuery();
                }

                //Execute SQL command to delete sending off data from database                                  
                using (SqlCeCommand sendoff_delete = new SqlCeCommand(sendoff_delete_command, conn))
                {
                    sendoff_delete.ExecuteNonQuery();
                }

                //Execute SQL command to delete goal data from database                                  
                using (SqlCeCommand goal_delete = new SqlCeCommand(goal_delete_command, conn))
                {
                    goal_delete.ExecuteNonQuery();
                }

                //Execute SQL command to delete match data from database                                  
                using (SqlCeCommand match_delete = new SqlCeCommand(match_delete_command, conn))
                {
                    match_delete.ExecuteNonQuery();
                }

                //Execute SQL command to delete home player data from database                                  
                using (SqlCeCommand home_player_delete = new SqlCeCommand(home_player_delete_command, conn))
                {
                    home_player_delete.ExecuteNonQuery();
                }

                //Execute SQL command to delete away player data from database                                  
                using (SqlCeCommand away_player_delete = new SqlCeCommand(away_player_delete_command, conn))
                {
                    away_player_delete.ExecuteNonQuery();
                }

                //Execute SQL command to delete home club data from database                                  
                using (SqlCeCommand home_club_delete = new SqlCeCommand(home_club_delete_command, conn))
                {
                    home_club_delete.ExecuteNonQuery();
                }

                //Execute SQL command to delete away club data from database                                  
                using (SqlCeCommand away_club_delete = new SqlCeCommand(away_club_delete_command, conn))
                {
                    away_club_delete.ExecuteNonQuery();
                }                               
                
                //Remove match from system and list on screen
                matches.RemoveAt(matchIndex);
                refOffLst.Items.RemoveAt(matchIndex);

                //Re-seed the database
                getMaximumDBValues(desktop_connection);
            }
            //If no Report is selected inform the user
            else
            {
                MessageBox.Show("Please select a Match to delete");
            }
        }        
    }
}
